﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;

namespace CRMBiz
{
    public static class ApplicationSACCHandler
    {
        public static DataSet SelectVerificationStatusData(int app_id)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_appid",app_id)
                };
                string query = "SELECT *,(SELECT count(IDCheckFile) FROM crm_idcheckhistory WHERE app_id=@p_appid) AS IDCheckFiles, (SELECT count(BankretrieveFile) FROM crm_bankcheckhistory WHERE app_id=@p_appid) AS BankFiles, (SELECT count(PayslipFile) FROM crm_payslipcheckhistory WHERE app_id=@p_appid) AS PayslipFiles FROM `sacc_verificationstatus` WHERE app_id=@p_appid";
                ds = DBhelper.ExecuteDataset(query, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet SelectFile(int app_id, string tablename, string fieldName)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_appid",app_id)
                };
                string sbWhere = string.Format("Select {0} as files from {1} where app_id=@p_appid", fieldName, tablename);
                ds = DBhelper.ExecuteDataset(sbWhere, para);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable getFormIdwithComesFrom(int app_id)
        {
            DataTable dt;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_appid",app_id)
                };
                dt = DBhelper.ExecuteDataset("SELECT af.AppFormId, a.comesfrom FROM crm_application a Inner Join crm_applicationappform af On af.ApplicationId=a.app_id Where a.app_id=@p_appid", para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static bool saveVerificationStatus(int app_id, string idcheck_status, int bankretrieve_attempts, string bankretrieve_status, string payslip_status, string creditehistory, string incomeexpense, string phoneverification, string contractprepared, string contractsigned, string fundtransferred, string historyOperator)
        {
            bool flag = false;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_app_id",app_id), 
                    DBhelper.SetParameter("@p_idcheck_status",bool.Parse(idcheck_status)?1:0),
                    DBhelper.SetParameter("@p_bankretrieve_attempts",bankretrieve_attempts),
                    DBhelper.SetParameter("@p_bankretrieve_status",bool.Parse(bankretrieve_status)?1:0),
                    DBhelper.SetParameter("@p_payslip_status",bool.Parse(payslip_status)?1:0),
                    DBhelper.SetParameter("@p_creditehistory",bool.Parse(creditehistory)?1:0),
                    DBhelper.SetParameter("@p_incomeexpense",bool.Parse(incomeexpense)?1:0),
                    DBhelper.SetParameter("@p_phoneverification",bool.Parse(phoneverification)?1:0),
                    DBhelper.SetParameter("@p_contractprepared",bool.Parse(contractprepared)?1:0),
                    DBhelper.SetParameter("@p_contractsigned",bool.Parse(contractsigned)?1:0),
                    DBhelper.SetParameter("@p_fundtransferred",bool.Parse(fundtransferred)?1:0),
                    DBhelper.SetParameter("@p_hOperator",historyOperator)
                };
                DBhelper.ExecuteNonQuerySP("sp_saveSACCverification", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool UpdateDownloadCount(string fieldName, int newCount, int app_id)
        {
            bool flag = false;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_appid",app_id)
                };
                string sbWhere = string.Format("Update sacc_verificationstatus SET {0} = {1} Where app_id=@p_appid", fieldName, newCount);
                DBhelper.ExecuteNonQuery(sbWhere, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
